Imports MyClasses
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.Common

Public Class dbAccess

    Public dbConnection As New SqlConnection
    Public dbTransaction As SqlTransaction
    Dim myConnString As String

    Public Sub New()

        myConnString = Trim(ConfigurationManager.AppSettings("connectionString"))
        'myConnString = "Data Source=.;Initial Catalog=NGSPaintsDB;Integrated Security=True" '"server=" & Trim(ConfigurationManager.AppSettings("Server")) & ";User ID=" & Trim(ConfigurationManager.AppSettings("UID")) & ";Password=" & Trim(ConfigurationManager.AppSettings("PWD")) & ";Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"

        'myConnString = "Data Source=.;Initial Catalog=NGSPaintsDB;Integrated Security=True" '"server=" & Trim(ConfigurationManager.AppSettings("Server")) & ";User ID=" & Trim(ConfigurationManager.AppSettings("UID")) & ";Password=" & Trim(ConfigurationManager.AppSettings("PWD")) & ";Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"
        OpenSqlCon()
    End Sub

    'Public Sub New(ByVal database As String, ByVal username As String, ByVal password As String)
    '    'myConnString = "server=192.168.0.50;User ID=sa;Password=redhead;Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"
    '    myConnString = "server=" & Trim(ConfigurationManager.AppSettings("Server")) & ";User ID=" & Trim(ConfigurationManager.AppSettings("UID")) & ";Password=" & Trim(ConfigurationManager.AppSettings("PWD")) & ";Database=" & Trim(ConfigurationManager.AppSettings("DbName")) & ";"
    '    OpenSqlCon()
    'End Sub

    Public Sub OpenSqlCon()
        Try
            dbConnection = New SqlConnection(myConnString)
            dbConnection.Open()
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "OpenSqlCon")
        End Try
    End Sub

    Public Sub closeSqlcon()
        Try
            If IsNothing(dbConnection) = False Then
                If dbConnection.State <> ConnectionState.Closed Then
                    dbConnection.Close()
                End If
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "closeSqlcon")
        End Try
    End Sub

    Public Sub BeginTransaction()
        Try
            If IsNothing(dbConnection) = False Then
                If dbConnection.State <> ConnectionState.Closed Then
                    'dbTransaction = dbConnection.BeginTransaction(IsolationLevel.Chaos)
                    'dbTransaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted)
                    dbTransaction = dbConnection.BeginTransaction
                End If
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "BeginTransaction")
        End Try
    End Sub

    Public Sub CommitTransaction()
        Try
            If IsNothing(dbConnection) = False And IsNothing(dbTransaction) = False Then
                If dbConnection.State <> ConnectionState.Closed Then
                    dbTransaction.Commit()
                End If
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "CommitTransaction")
        Finally
            dbTransaction = Nothing
        End Try
    End Sub

    Public Sub RollBackTransaction()
        Try
            If IsNothing(dbConnection) = False And IsNothing(dbTransaction) = False Then
                If dbConnection.State <> ConnectionState.Closed Then
                    dbTransaction.Rollback()
                End If
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "RollBackTransaction")
        Finally
            dbTransaction = Nothing
        End Try
    End Sub

    Public Function ExecuteNonQuery(ByVal sql As String, ByVal params() As SqlParameter) As Integer
        Try
            Dim cmd As New SqlCommand(sql, dbConnection)
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next
            If Not IsNothing(dbTransaction) Then
                cmd.Transaction = dbTransaction
            End If
            Dim retval As Integer = cmd.ExecuteNonQuery()
            Return retval
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteNonQuery")
            Return 0
        End Try
    End Function

    Public Function ExecuteNonQuery(ByVal sql As String) As Integer
        Try
            Dim cmd As New SqlCommand(sql, dbConnection)
            If Not IsNothing(dbTransaction) Then
                cmd.Transaction = dbTransaction
            End If
            Dim retval As Integer = cmd.ExecuteNonQuery()
            Return retval
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteNonQuery")
            Return 0
        End Try
    End Function

    Public Function ExecuteDataSet(ByVal sql As String) As DataSet
        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(sql, dbConnection)
        Try
            da.Fill(ds)
            Return ds
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteNonQuery")
            Return Nothing
        End Try
    End Function

    Public Function ExecuteScalar(ByVal sql As String, ByVal params() As SqlParameter) As Object
        Try
            Dim cmd As New SqlCommand(sql, dbConnection)
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next
            If Not IsNothing(dbTransaction) Then
                cmd.Transaction = dbTransaction
            End If
            Dim retval As Object = cmd.ExecuteScalar
            Return retval
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteScalar")
            Return Nothing
        End Try
    End Function

    Public Function ExecuteScalar(ByVal sql As String) As Object
        Try
            Dim cmd As New SqlCommand(sql, dbConnection)
            If Not IsNothing(dbTransaction) Then
                cmd.Transaction = dbTransaction
            End If

            Dim retval As Object = cmd.ExecuteScalar
            Return retval
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteScalar")
            Return Nothing
        End Try
    End Function

    Public Function ExecuteDataSet(ByVal sql As String, ByVal params() As SqlParameter) As DataSet
        Dim ds As New DataSet
        Try
            Dim da As New SqlDataAdapter(sql, dbConnection)

            For i As Integer = 0 To params.Length - 1
                da.SelectCommand.Parameters.Add(params(i))
            Next
            da.Fill(ds)
            Return ds
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteDataSet")
            Return Nothing
        End Try
    End Function

    Public Function ExecuteDataSetSP(ByVal spname As String, ByVal params() As SqlParameter) As DataSet
        Dim ds As New DataSet
        Try
            Dim cmd As New SqlCommand(spname, dbConnection)
            cmd.CommandType = CommandType.StoredProcedure
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next

            Dim da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            Return ds
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteDataSet")
            Return Nothing
        End Try
    End Function

    Public Function ExecuteSP(ByVal spname As String, ByVal params() As SqlParameter) As Integer
        Try
            Dim cmd As New SqlCommand(spname, dbConnection)
            cmd.CommandType = CommandType.StoredProcedure
            For i As Integer = 0 To params.Length - 1
                cmd.Parameters.Add(params(i))
            Next
            Dim retval As Integer = cmd.ExecuteNonQuery()
            Return retval
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteSP")
            Return Nothing
        End Try
    End Function

    Public Sub RunNonQuery(ByVal WhichTable As String, ByRef sqlParams As SqlParameter())
        Dim cmdUPD As New SqlCommand
        Dim intCnt As Integer
        Try
            If IsNothing(dbTransaction) = False Then
                cmdUPD = New SqlCommand(WhichTable, dbConnection, dbTransaction)
            Else
                cmdUPD = New SqlCommand(WhichTable, dbConnection)
            End If

            cmdUPD.CommandType = CommandType.Text
            If Not sqlParams Is Nothing And UBound(sqlParams) >= 0 Then
                Dim par As SqlParameter
                For Each par In sqlParams
                    cmdUPD.Parameters.Add(par)
                Next par
            End If
            intCnt = cmdUPD.ExecuteNonQuery()
        Catch ex As Exception
            Throw ex
        Finally
            cmdUPD.Dispose()
            cmdUPD = Nothing
        End Try
    End Sub

    Public Sub RunSelectQuery(ByVal sqlQuery As String, ByRef objDataSet As DataSet)
        Dim objDataAdapter As New SqlDataAdapter
        Try
            objDataAdapter = New SqlDataAdapter(sqlQuery, dbConnection)
            If IsNothing(dbTransaction) = False Then
                objDataAdapter.SelectCommand.Transaction = dbTransaction
            End If
            objDataSet.EnforceConstraints = False
            objDataSet.Clear()
            objDataAdapter.Fill(objDataSet)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "RunSelectQuery")
        Finally
            objDataAdapter.Dispose()
            objDataAdapter = Nothing
        End Try
    End Sub

    Public Sub RunSelectQuery(ByVal sqlQuery As String, ByRef objDataTable As DataTable)

        Dim objDataAdapter As New SqlDataAdapter
        Try
            objDataAdapter = New SqlDataAdapter(sqlQuery, dbConnection)
            If IsNothing(dbTransaction) = False Then
                objDataAdapter.SelectCommand.Transaction = dbTransaction
            End If
            objDataTable.Clear()
            objDataAdapter.Fill(objDataTable)
        Catch ex As Exception
            Throw ex
        Finally
            objDataAdapter.Dispose()
            objDataAdapter = Nothing
        End Try
    End Sub
    Public Function ExecuteDataTable(ByVal sql As String) As DataTable
        Dim objDataTable As New DataTable
        Try
            RunSelectQuery(sql, objDataTable)
            Return objDataTable
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "dbAccess", "ExecuteDataTable")
        End Try
    End Function
End Class


